﻿Imports System.Data.OleDb

Public Class AnswerRatingGateway
    Inherits DataGateway

    Protected Overrides ReadOnly Property FindAllQuery As String
        Get
            Return "SELECT * FROM AnswerRating"
        End Get
    End Property

    Protected Overrides ReadOnly Property DeleteQuery As String
        Get
            Return "DELETE FROM AnswerRating"
        End Get
    End Property

    Protected Overrides ReadOnly Property SiteUserQuery() As String
        Get
            Return "SELECT SiteUser.* " &
                   "FROM AnswerRating LEFT JOIN SiteUser " &
                   "ON AnswerRating.UserID=SiteUser.ID " &
                   "WHERE AnswerRating.ID=@ID"
        End Get
    End Property

    Public Overloads Sub DeleteByID(ByVal answerID As ULong, ByVal userID As ULong)
        Dim sqlQuery As String = DeleteQuery & " WHERE AnswerID=@AnswerID AND UserID=@UserID"
        Using connection As New OleDbConnection(ConnectionString)
            Dim command As New OleDbCommand(sqlQuery, connection)
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@AnswerID", answerID)
            command.Parameters.AddWithValue("@UserID", userID)
            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Sub

    Public Overloads Function FindByID(ByVal answerID As ULong, ByVal userID As ULong) As DataTable
        Dim sqlQuery As String = FindAllQuery & "  WHERE UserID=@UserID AND AnswerID=@AnswerID"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@UserID", userID)
        queryParams.Add("@AnswerID", answerID)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Function UserHasRated(ByVal answerID As ULong, ByVal userID As ULong) As Boolean
        Dim rows As DataRowCollection = FindByID(answerID, userID).Rows
        Return rows.Count() = 1
    End Function

    Public Sub InsertOrUpdate(ByVal answerID As ULong, ByVal userID As ULong, ByVal rating As Integer)
        If UserHasRated(answerID, userID) Then
            Update(answerID, userID, rating)
        Else
            Insert(answerID, userID, rating)
        End If
    End Sub

    Public Function Insert(ByVal answerID As ULong, ByVal userID As ULong, ByVal rating As Integer) As ULong
        Dim newID As ULong
        Dim sqlQuery As String = "INSERT INTO AnswerRating (AnswerID, Rating, UserID) VALUES (@AnswerID, @Rating, @UserID)"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@AnswerID", answerID)
                command.Parameters.AddWithValue("@Rating", rating)
                command.Parameters.AddWithValue("@UserID", userID)
                connection.Open()
                command.ExecuteNonQuery()
                newID = GetLastInsertID(command)
            End Using
        End Using

        Return newID
    End Function

    Public Sub Update(ByVal answerID As ULong, ByVal userID As ULong, ByVal rating As Integer)
        Dim sqlQuery As String = "UPDATE AnswerRating SET Rating=@Rating WHERE AnswerID=@AnswerID AND UserID=@UserID"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Rating", rating)
                command.Parameters.AddWithValue("@AnswerID", answerID)
                command.Parameters.AddWithValue("@UserID", userID)
                connection.Open()
                Dim rowsAffected As Integer = command.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class
